package ibmetp.mis.dao;

import ibmetp.mis.vo.Dept;
import ibmetp.mis.vo.Page;
import ibmetp.mis.vo.User;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 部门数据库操作类
 * @author LIN A ER
 * @created 2011-10-6
 */
public class DeptDao extends BaseDao {

	@Override
	/**
	 * 将数据集合转换为实体类列表
	 */
	public List ResultSet2List(ResultSet rs) {
		// TODO Auto-generated method stub
		List<Dept> resultList=new ArrayList<Dept>();
		UserDao ud=new UserDao();
		try {
			while(rs!=null&&rs.next())
			{
				Dept dept=new Dept();
				dept.setDeptId(rs.getLong("DEPT_ID"));
				dept.setDeptNo(rs.getString("DEPT_NO"));
				dept.setDeptName(rs.getString("DEPT_NAME"));
				dept.setManager(ud.getUserById(rs.getLong("MANAGER_ID")));
				dept.setCreator(ud.getUserById(rs.getLong("CREATE_BY")));
				dept.setCreateDate(rs.getDate("CREATE_DATE"));
				dept.setUpdater(ud.getUserById(rs.getLong("UPDATE_BY")));
				if(rs.getLong("MANAGER_ID")>0)//by LAN DABIN 2011-10-10
				{
					User user=new User();
					user.setUserId(rs.getLong("MANAGER_ID"));
					user.setUserName(rs.getString("USER_NAME"));
					dept.setManager(user);
				}
				//dept.setUpdateDate(rs.getDate("UPDATE_DATE"));
				resultList.add(dept);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultList;
	}

	@Override
	/**
	 * 添加查询条件
	 */
	public Map<Integer, Object[]> getCondition(Object o) {
		// TODO Auto-generated method stub
		Map<Integer, Object[]> paramsMap = new HashMap<Integer, Object[]>();
		Integer count = 0;
		Dept dept=(Dept)o;
		if(dept.getDeptId()!=null&&dept.getDeptId()>0)
		{
			count++;
			paramsMap.put(count, new Object[]{"and DEPT_ID like ?","%"+dept.getDeptId()+"%"});
		}
		if(dept.getDeptNo()!=null&&dept.getDeptNo().trim().length()>0)
		{
			count++;
			paramsMap.put(count, new Object[]{"and DEPT_NO like ?","%"+dept.getDeptNo()+"%"});
		}
		if(dept.getDeptName()!=null&&dept.getDeptName().trim().length()>0)
		{
			count++;
			paramsMap.put(count, new Object[]{"and DEPT_NAME like ?","%"+dept.getDeptName()+"%"});
		}
		if(dept.getManager()!=null)
		{
			count++;
			//paramsMap.put(count, new Object[]{"and MANAGER_ID like ?","%"+dept.getManager().getUserId()+"%"});
			paramsMap.put(count, new Object[]{"and MANAGER_ID =?",dept.getManager().getUserId()});//by LAN DABIN
		}
		return paramsMap;
	}
	/**
	 * 按部门id查询部门
	 * @param id部门id
	 * @return
	 */
    public Dept getDeptById(Long id)
    {
    	String sql="select d.*,u.USER_NAME from BAS_T_DEPT d,BAS_T_USER u WHERE d.DEPT_ID = ? and d.MANAGER_ID=u.USER_ID(+)";
    	conn=DBUtil.getConnection();
    	PreparedStatement ps;
    	Dept dept=null;
    	try {
			ps=conn.prepareStatement(sql);
			ps.setLong(1, id);
			ResultSet rs=ps.executeQuery();
			
			while(rs!=null&&rs.next())
			{
				dept=new Dept();
				dept.setDeptId(rs.getLong("DEPT_ID"));
				dept.setDeptNo(rs.getString("DEPT_NO"));
				dept.setDeptName(rs.getString("DEPT_NAME"));
				
				User user=null;
				UserDao ud=new UserDao();
				user=ud.getUserById(rs.getLong("MANAGER_ID"));
				dept.setManager(user);
				
				user=ud.getUserById(rs.getLong("CREATE_BY"));
				dept.setCreator(user);
				
				dept.setCreateDate(rs.getDate("CREATE_DATE"));
				
				user=ud.getUserById(rs.getLong("UPDATE_BY"));
				dept.setUpdater(user);
				
				dept.setUpdateDate(rs.getDate("UPDATE_DATE"));				
			}
			ps.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dept;    	
    }
    /**
     * 通过部门编号查找部门
     * @param No 部门编号
     * @return
     */
    public Dept getDeptByNo(String No)
    {
    	String sql="select * from BAS_T_DEPT WHERE DEPT_NO LIKE ?";
    	conn=DBUtil.getConnection();
    	PreparedStatement ps;
    	Dept dept=null;
    	try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, No);
			ResultSet rs=ps.executeQuery();
			
			while(rs!=null&&rs.next())
			{
				dept=new Dept();
				dept.setDeptId(rs.getLong("DEPT_ID"));
				dept.setDeptNo(rs.getString("DEPT_NO"));
				dept.setDeptName(rs.getString("DEPT_NAME"));
				
				User user=null;
				UserDao ud=new UserDao();
				user=ud.getUserById(rs.getLong("MANAGET_ID"));
				dept.setManager(user);
				
				user=ud.getUserById(rs.getLong("CREATE_BY"));
				dept.setCreator(user);
				
				dept.setCreateDate(rs.getDate("CREATE_DATE"));
				
				user=ud.getUserById(rs.getLong("UPDATE_BY"));
				dept.setUpdater(user);
				
				dept.setUpdateDate(rs.getDate("UPDATE_DATE"));				
			}
			ps.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dept;    	
    }
    /**
     * 添加部门
     * @param dept新部门信息
     */
    public void addDept(Dept dept)
    {
    	String sql="insert into BAS_T_DEPT(DEPT_ID,DEPT_NO,DEPT_NAME,CREATE_DATE) values(DEPT_SEQ.NEXTVAL,?,?,sysdate)";
    	conn=DBUtil.getConnection();
    	PreparedStatement ps;
    	
    	try {
			ps=conn.prepareStatement(sql);
			//ps.setLong(1, dept.getDeptId());
			ps.setString(1, dept.getDeptNo());
			ps.setString(2, dept.getDeptName());
			//ps.setLong(3, 1);//dept.getManager().getUserId());
			//ps.setLong(3, 2);//dept.getCreator().getUserId());
			//ps.setDate(5, 'sysdate');(Date) dept.getCreateDate());
			//ps.setLong(5,1);// dept.getUpdater().getUserId());
			//ps.setDate(7, (Date) dept.getUpdateDate());
			ResultSet rs=ps.executeQuery();
			ps.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    /**
     * 设置部门经理
     * @param deptId 部门Id
     * @param mgrId  用户Id
     * @author LAN DABIN
     */
    public void setManager(Long deptId,Long mgrId)
    {
    	String sql="update BAS_T_DEPT set MANAGER_ID=? where DEPT_ID=?";
    	conn=DBUtil.getConnection();
    	PreparedStatement ps;
    	
    	try {
			ps=conn.prepareStatement(sql);
			ps.setLong(1,mgrId);
		    ps.setLong(2,deptId);
		    //ps.setLong(4, dept.getUpdater().getUserId());
		    //ps.setDate(5, (Date) dept.getUpdateDate());
		    
		    ResultSet rs=ps.executeQuery();
		    ps.close();
		    conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    /**
     * 更新部门信息
     * @param dept部门新信息
     */
    public void updateDept(Dept dept)
    {
    	String sql="update BAS_T_DEPT set DEPT_NO=?,DEPT_NAME=? where DEPT_ID=?";
    	conn=DBUtil.getConnection();
    	PreparedStatement ps;
    	
    	try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, dept.getDeptNo());
			ps.setString(2, dept.getDeptName());
		    ps.setLong(3, dept.getDeptId());
		    //ps.setLong(4, dept.getUpdater().getUserId());
		    //ps.setDate(5, (Date) dept.getUpdateDate());
		    
		    ResultSet rs=ps.executeQuery();
		    ps.close();
		    conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    }
    /**
     * 通过部门id删除部门
     * @param id部门id
     */
    public void deleteDeptById(Long id)
    {
    	String sql="delete from BAS_T_DEPT where DEPT_ID=?";
    	conn=DBUtil.getConnection();
    	PreparedStatement ps;
    	
    	try {
			ps=conn.prepareStatement(sql);
			ps.setLong(1, id);
			ResultSet rs=ps.executeQuery();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    public void deleteDeptByIds(String ids)
    {
    	String sql="delete from BAS_T_DEPT where DEPT_ID IN ("+ids+")";
    	conn=DBUtil.getConnection();
    	PreparedStatement ps;
    	
    	try {
			ps=conn.prepareStatement(sql);
			ResultSet rs=ps.executeQuery();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    /**
     * 通过部门编号删除部门
     * @param no部门编号
     */
    public void deleteDeptByNo(String no)
    {
    	String sql="delete from BAS_T_DEPT where DEPT_NO=?";
    	conn=DBUtil.getConnection();
    	PreparedStatement ps;
    	
    	try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, no);
			ResultSet rs=ps.executeQuery();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
	/**
	 * 查询部门信息
	 * @param pageNo 当前页面
	 * @param pageSize页面大小
	 * @param user 用户条件
	 * @return
	 */
	public Page getUserPage(int pageNo, int pageSize, Object dept) {

		return this.find(dept, pageNo, pageSize, "(SELECT d.*,u.USER_NAME FROM BAS_T_DEPT d,BAS_T_USER u where d.MANAGER_ID=u.USER_ID(+))");
	}
    
}
